package com.cnsuning.jobtool.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import javax.validation.constraints.NotNull;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ExcelUtils {
    /**
     * 动态解析Excel文件，并返回指定的对象列表
     *
     * @param excelFile
     * @param sheetColumnsMap  sheet名与对应的sheet下的列名
     * @return
     */
    public static Map<String,List<LinkedHashMap<String,Object>>> readExcelHasTitle(File excelFile, @NotNull Map<String,List<String>> sheetColumnsMap) throws IOException, InvalidFormatException {
        //记录结果
        Map<String,List<LinkedHashMap<String,Object>>> result = new HashMap<>();
        //载入文件
        Workbook workbook= WorkbookFactory.create(excelFile);

        //记录当前标题与列位置的映射
        Map<String,String> titleMap=new LinkedHashMap<>();
        Set<String> keys = sheetColumnsMap.keySet();

        //遍历所有Sheet页
        int pageNum=workbook.getNumberOfSheets();
        for(int page=0;page<pageNum;page++)
        {
            List<LinkedHashMap<String,Object>> sheetResult = new ArrayList<>();
            //获取Sheet
            Sheet sheet=workbook.getSheetAt(page);

            boolean containsKey = false;
            String currentKey = "";
            for (String key : keys)
            {
                if (sheet.getSheetName().equals(key))
                {
                    containsKey = true;
                    currentKey = key;
                    break;
                }
            }
            if (!containsKey) continue;

            List<String> columns = sheetColumnsMap.get(currentKey);
            if (columns == null){
                columns = new ArrayList<>();
            }
            //遍历全部非空行
            Iterator<Row> rowItr=sheet.rowIterator();
            int rowNum=0;
            while(rowItr.hasNext())
            {
                //获取行
                Row row=rowItr.next();
                if (isRowEmpty(row)) continue;
                //记录数据
                LinkedHashMap<String,Object> map=new LinkedHashMap<>();

                //遍历本行全部列（包括空列）
                int colNum=row.getLastCellNum();
                for(int index=0;index<colNum;index++)
                {
                    //获取单元格
                    Cell cell=row.getCell(index);
                    Object value = null;
                    if (cell != null){
                        //获取单元格内容
                        value =getCellValue(cell);
                    }


                    //第一行为标题行，记录标题和位置索引的映射
                    if(rowNum==0)
                    {
                        String title=value+"";
                        if(columns.contains(title))
                            titleMap.put(index+"",title);
                    }
                    //非标题行，根据指定columnMap和PatternKey查找记录
                    else
                    {
                        //需要的内容
                        if(!titleMap.containsKey(index+"")) continue;
                        map.put(titleMap.get(index+""),value);
                    }
                }

                if(rowNum!=0) {
                    sheetResult.add(map);
                }

                rowNum++;
            }
            result.put(sheet.getSheetName(),sheetResult);
        }

        return result;
    }

    public static List<LinkedHashMap<String,Object>> readExcelHasTitleOneSheet(File excelFile, String sheetName) throws IOException, InvalidFormatException {
        //载入文件
        Workbook workbook= WorkbookFactory.create(excelFile);
        int pageNum=workbook.getNumberOfSheets();
        String currentsheetName = "";
        for(int page=0;page<pageNum;page++){
            String pageSheetName = workbook.getSheetName(page);
            if (pageSheetName.contains(sheetName)){
                currentsheetName = pageSheetName;
                break;
            }
        }

        if (StringUtils.isBlank(currentsheetName))
        {
            throw new RuntimeException("无此sheet");
        }

        Sheet sheet = workbook.getSheet(currentsheetName);
        //遍历全部非空行
        Iterator<Row> rowItr=sheet.rowIterator();
        List<String> columns = new ArrayList<>();
        if (rowItr.hasNext())
        {
            Row row = rowItr.next();
            //遍历本行全部列（包括空列）
            int colNum=row.getLastCellNum();
            for(int index=0;index<colNum;index++)
            {
                String column = (String) getCellValue(row.getCell(index));
                columns.add(column);
            }
        }
        Map<String,List<String>> sheetColumnsMap = new HashMap<>();
        sheetColumnsMap.put(currentsheetName,columns);
        Map<String, List<LinkedHashMap<String, Object>>> resultMap = readExcelHasTitle(excelFile, sheetColumnsMap);
        return resultMap.get(currentsheetName);
    }

    public static List<List<Object>> readExcelNoTitleOneSheet(File excelFile, String sheetName) throws IOException, InvalidFormatException {
        //载入文件
        Workbook workbook= WorkbookFactory.create(excelFile);
        int pageNum=workbook.getNumberOfSheets();
        String currentsheetName = "";
        for(int page=0;page<pageNum;page++){
            String pageSheetName = workbook.getSheetName(page);
            if (pageSheetName.contains(sheetName)){
                currentsheetName = pageSheetName;
                break;
            }
        }

        if (StringUtils.isBlank(currentsheetName))
        {
            throw new RuntimeException("无此sheet");
        }

        Sheet sheet = workbook.getSheet(currentsheetName);
        //遍历全部非空行
        Iterator<Row> rowItr=sheet.rowIterator();
        List<List<Object>> result = new ArrayList<>();
        while(rowItr.hasNext()){
            //获取行
            Row row=rowItr.next();
            if (isRowEmpty(row)) continue;
            //记录数据
            List<Object> rowData = new ArrayList<>();
            //遍历本行全部列（包括空列）
            int colNum=row.getLastCellNum();
            for(int index=0;index<colNum;index++){
                //获取单元格
                Cell cell=row.getCell(index);
                Object value = null;
                if (cell != null){
                    //获取单元格内容
                    value =getCellValue(cell);
                }
                rowData.add(value);

            }
            //
            result.add(rowData);
        }
        return result;
    }
    /**
     * 依据Excel中Cell类型读取不同的值
     *
     * @param cell
     * @return String/Double/Date/boolean/null
     */
    private static Object getCellValue(Cell cell)
    {
        Object result=null;

        //获取类型
        int type=cell.getCellType();
        //根据不同类型处理
        switch(type)
        {
            //字符串
            case Cell.CELL_TYPE_STRING:
            {
                result=cell.getRichStringCellValue().toString().trim();
                break;
            }
            //数字(Double)
            case Cell.CELL_TYPE_NUMERIC:
                //公式(Double)
            case Cell.CELL_TYPE_FORMULA:
            {
                //日期类型判断
                //日期类型
                if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell))
                {
                    result=cell.getDateCellValue();
                }
                //普通数字
                else
                {
                    result=new Double(cell.getNumericCellValue());
                }
                break;
            }
            //布尔
            case Cell.CELL_TYPE_BOOLEAN:
            {
                result=cell.getBooleanCellValue();
                break;
            }
            //其他
            default:
        }

        return result;
    }


    private static boolean isRowEmpty(Row row){
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
            return false;
        }
        return true;
    }
}
